﻿'
' Boogaloo® - http://www.mindfly.com
' Copyright (c) 2009
' by Mindfly, Inc.
'
' Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated 
' documentation files (the "Software"), to deal in the Software without restriction, including without limitation 
' the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and 
' to permit persons to whom the Software is furnished to do so, subject to the following conditions:
'
' The above copyright notice and this permission notice shall be included in all copies or substantial portions 
' of the Software.
'
' THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES,
' INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
' DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
' SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
' SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
' WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
' THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Imports System.Data.SqlClient
Imports Boogaloo.Base.DAL

Namespace DAL.SqlClient


    Public Class SqlCommentDataProvider
        Inherits CommentDataProvider


        ''' <summary>
        ''' Creates a Comment Details from database record
        ''' </summary>
        ''' <param name="comment_id">Integer (PK)</param>
        ''' <returns>CommentDetails</returns>
        ''' <remark>rss - 4/5/2009</remark>
        Public Overrides Function GetComment(ByVal comment_id As Integer) As CommentDetails
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("boogaloo_core_comment_GetComment_sp", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@comment_id", SqlDbType.Int).Value = comment_id
                cn.Open()
                Dim reader As SqlDataReader = ExecuteReader(cmd)
                If reader.Read Then
                    Return GetCommentDetailsFromReader(reader)
                Else
                    Return Nothing
                End If
            End Using
        End Function

        Public Overrides Function GetCommentByCommentyKey(ByVal comment_key As System.Guid) As CommentDetails
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("boogaloo_core_comment_GetCommentByCommentyKey_sp", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@comment_key", SqlDbType.UniqueIdentifier).Value = comment_key
                cn.Open()
                Dim reader As SqlDataReader = ExecuteReader(cmd)
                If reader.Read Then
                    Return GetCommentDetailsFromReader(reader)
                Else
                    Return Nothing
                End If
            End Using
        End Function


        ''' <summary>
        ''' Creates a collection of CommentDetails from a database recordset
        ''' </summary>
        ''' <returns>List(Of CommentDetails)</returns>
        ''' <remark>rss - 4/5/2009</remark>
        Public Overloads Overrides Function GetComments(ByVal entity_key As Guid) As List(Of CommentDetails)
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("boogaloo_core_comment_GetComments_sp", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@entity_key", SqlDbType.UniqueIdentifier).Value = entity_key
                cn.Open()
                Return GetCommentDetailsCollectionFromReader(ExecuteReader(cmd))
            End Using
        End Function

        ''' <summary>
        ''' Creates a collection of CommentDetails from a database recordset
        ''' </summary>
        ''' <returns>List(Of CommentDetails)</returns>
        ''' <remark>rss - 7/12/2009</remark>
        Public Overloads Overrides Function GetComments(ByVal root_id As Integer, ByVal max_count As Integer) As List(Of CommentDetails)
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("boogaloo_core_comment_GetLastComments_sp", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@root_id", SqlDbType.Int).Value = root_id
                cmd.Parameters.Add("@max_count", SqlDbType.Int).Value = max_count
                cn.Open()
                Return GetCommentDetailsCollectionFromReader(ExecuteReader(cmd))
            End Using
        End Function

        ''' <summary>
        ''' Creates a collection of CommentDetails from a database recordset
        ''' </summary>
        ''' <returns>List(Of CommentDetails)</returns>
        ''' <remark>rss - 7/12/2009</remark>
        Public Overrides Function GetCommentsByDateRange(ByVal root_id As Integer, ByVal start_date As DateTime, ByVal end_date As DateTime) As List(Of CommentDetails)
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("boogaloo_core_comment_GetCommentsByDateRange_sp", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@root_id", SqlDbType.Int).Value = root_id
                cmd.Parameters.Add("@start_date", SqlDbType.DateTime).Value = start_date
                cmd.Parameters.Add("@end_date", SqlDbType.DateTime).Value = end_date
                cn.Open()
                Return GetCommentDetailsCollectionFromReader(ExecuteReader(cmd))
            End Using
        End Function

        ''' <summary>
        ''' Creates a collection of CommentDetails from a database recordset
        ''' </summary>
        ''' <returns>List(Of CommentDetails)</returns>
        ''' <remark>rss - 7/12/2009</remark>
        Public Overrides Function GetCommentsNotApproved(ByVal root_id As Integer) As List(Of CommentDetails)
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("boogaloo_core_comment_GetCommentsNotApproved_sp", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@root_id", SqlDbType.Int).Value = root_id
                cn.Open()
                Return GetCommentDetailsCollectionFromReader(ExecuteReader(cmd))
            End Using
        End Function

        ''' <summary>
        ''' Inserts a record representing an CommentDetails into the database
        ''' </summary>
        ''' <returns>Integer (PK of record inserted)</returns>
        ''' <remark>rss - 4/5/2009</remark>
        Public Overrides Function InsertComment(ByVal record As CommentDetails) As Integer
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("boogaloo_core_comment_InsertComment_sp", cn)
                cmd.CommandType = CommandType.StoredProcedure
                With cmd.Parameters
                    .Add("@entity_key", SqlDbType.UniqueIdentifier).Value = record.entity_key
                    .Add("@author", SqlDbType.NVarChar, 50).Value = DataObject.ConvertEmptyStringToDBNull(record.author)
                    .Add("@email", SqlDbType.NVarChar, 50).Value = DataObject.ConvertEmptyStringToDBNull(record.email)
                    .Add("@website", SqlDbType.NVarChar, 255).Value = DataObject.ConvertEmptyStringToDBNull(record.website)
                    .Add("@date_added", SqlDbType.DateTime).Value = record.date_added
                    .Add("@body", SqlDbType.NVarChar).Value = DataObject.ConvertEmptyStringToDBNull(record.body)
                    .Add("@ip", SqlDbType.NVarChar, 50).Value = record.ip
                    .Add("@approved", SqlDbType.Bit).Value = record.approved
                    .Add("@notify", SqlDbType.Bit).Value = record.notify
                    .Add("@spam", SqlDbType.Bit).Value = record.spam
                    .Add("@last_modified_by", SqlDbType.NVarChar, 50).Value = record.last_modified_by
                    .Add("@comment_id", SqlDbType.Int).Direction = ParameterDirection.Output
                End With
                cn.Open()
                Dim ret As Integer = ExecuteNonQuery(cmd)
                Return CInt(cmd.Parameters("@comment_id").Value)
            End Using
        End Function

        ''' <summary>
        ''' Updates an existing CommentDetails record in the database
        ''' </summary>
        ''' <returns>Boolean (success)</returns>
        ''' <remark>rss - 4/5/2009</remark>
        Public Overrides Function UpdateComment(ByVal record As CommentDetails) As Boolean
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("boogaloo_core_comment_UpdateComment_sp", cn)
                cmd.CommandType = CommandType.StoredProcedure
                With cmd.Parameters
                    .Add("@comment_id", SqlDbType.Int).Value = record.comment_id
                    .Add("@entity_key", SqlDbType.UniqueIdentifier).Value = record.entity_key
                    .Add("@author", SqlDbType.NVarChar, 50).Value = DataObject.ConvertEmptyStringToDBNull(record.author)
                    .Add("@email", SqlDbType.NVarChar, 50).Value = DataObject.ConvertEmptyStringToDBNull(record.email)
                    .Add("@website", SqlDbType.NVarChar, 255).Value = DataObject.ConvertEmptyStringToDBNull(record.website)
                    .Add("@date_added", SqlDbType.DateTime).Value = record.date_added
                    .Add("@body", SqlDbType.NVarChar).Value = DataObject.ConvertEmptyStringToDBNull(record.body)
                    .Add("@approved", SqlDbType.Bit).Value = record.approved
                    .Add("@spam", SqlDbType.Bit).Value = record.spam
                    .Add("@notify", SqlDbType.Bit).Value = record.notify
                    .Add("@last_modified_by", SqlDbType.NVarChar, 50).Value = record.last_modified_by
                End With
                cn.Open()
                Return ExecuteNonQuery(cmd) = 1
            End Using
        End Function

        ''' <summary>
        ''' Deletes an existing CommentDetails record from the database
        ''' </summary>
        ''' <returns>Boolean (success)</returns>
        ''' <remark>rss - 4/5/2009</remark>

        Public Overrides Function DeleteComment(ByVal comment_id As Integer) As Boolean
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("boogaloo_core_comment_DeleteComment_sp", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@comment_id", SqlDbType.Int).Value = comment_id
                cn.Open()
                Return ExecuteNonQuery(cmd) = 1
            End Using
        End Function


    End Class


End Namespace